﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using EHR.DataModel;
using System.Data;

namespace CommunitySystem.BO.自定义查询
{
    public class DataQueryBo
    {
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public IList<ViewInfo> GetDataQuery()
        {
            DataSet tableDS = AccessHelper.GetInstance().ExcuteDataSet("SELECT Id, TableName, DisplayName FROM Web_TableInfo order by Sort");
            DataSet filedDS = AccessHelper.GetInstance().ExcuteDataSet("SELECT TableId, FieldName, DisplayName, AllowNull, FieldType, StringFormat, Width FROM Web_FieldInfo order by Sort");
            List<TableInfo> tables = new List<TableInfo>();//DataAccessor.QueryForList<TableInfo>("SelectTableInfo", null);
            List<FieldInfo> fields = new List<FieldInfo>();//DataAccessor.QueryForList<FieldInfo>("SelectFieldInfo", null);
            foreach (DataRow dr in tableDS.Tables[0].Rows)
            {
                TableInfo ti = new TableInfo();
                ti.TableName = dr["TableName"].ToString();
                ti.TableId = Guid.Parse(dr["ID"].ToString());
                ti.ViewName = dr["DisplayName"].ToString();
                tables.Add(ti);
            }

            foreach (DataRow dr in filedDS.Tables[0].Rows)
            {
                FieldInfo fi = new FieldInfo();
                fi.TableId = Guid.Parse(dr["TableId"].ToString());
                fi.FieldName = dr["FieldName"].ToString();
                fi.DisplayName = dr["DisplayName"].ToString();
                fi.AllowNull = Boolean.Parse(dr["AllowNull"].ToString());
                fi.FieldType = (EHR.Ctrls.QueryDesign.FieldType)Enum.Parse(typeof(EHR.Ctrls.QueryDesign.FieldType), dr["FieldType"].ToString());
                fi.StringFormat = dr["StringFormat"].ToString();
                fi.Width = int.Parse(dr["Width"].ToString());
                fields.Add(fi);
            }

            IDictionary<Guid, TableInfo> dic = new Dictionary<Guid, TableInfo>();
            foreach (TableInfo t in tables)
            {
                dic.Add(t.TableId, t);
            }
            foreach (FieldInfo f in fields)
            {
                if (dic.ContainsKey(f.TableId))
                {
                    TableInfo t = dic[f.TableId];
                    t.Columns.Add(f);
                }
            }
            List<ViewInfo> list = new List<ViewInfo>();
            foreach (TableInfo t in tables)
            {
                ViewInfo vi = new ViewInfo();
                vi.ViewID = t.TableId;
                vi.ViewName = string.IsNullOrEmpty(t.ViewName) ? t.TableName : t.ViewName;
                vi.XML = t.ToXml().ToString();
                list.Add(vi);
            }
            return list;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public IList<ViewInfo> GetQueryViewsQuery()
        {
            List<ViewInfo> list = new List<ViewInfo>();
            DataSet viewDS = AccessHelper.GetInstance().ExcuteDataSet("SELECT Id, ViewName, XML, ViewType FROM Web_ViewInfo order by CreateTime");
            foreach (DataRow dr in viewDS.Tables[0].Rows)
            {
                ViewInfo vi = new ViewInfo();
                vi.ViewID = Guid.Parse(dr["Id"].ToString());
                vi.ViewName = dr["ViewName"].ToString();
                vi.XML = dr["XML"].ToString();
                list.Add(vi);
            }
            return list;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="vi"></param>
        public void InserQuery(ViewInfo vi)
        {
            vi.CreateTime = DateTime.Now;
            string sql = "insert Web_ViewInfo(Id, ViewName, XML, CreateTime, ViewType) values ('{0}', '{1}', '{2}','{3}','{4}')";
            sql = string.Format(sql, vi.ViewID, vi.ViewName, vi.XML, vi.CreateTime.ToString(), vi.ViewType);

            AccessHelper.GetInstance().ExecuteNonQuery(sql);
        }

        public void UpdateView(ViewInfo vi)
        {
            string sql = "update Web_ViewInfo set ViewName = '{1}', XML = '{2}' where Id = '{0}'";
            sql = string.Format(sql, vi.ViewID, vi.ViewName, vi.XML);

            AccessHelper.GetInstance().ExecuteNonQuery(sql);
        }

        public void DeleteView(Guid id)
        {
            string sql = "delete from Web_ViewInfo where Id = '{0}'";
            sql = string.Format(sql, id);

            AccessHelper.GetInstance().ExecuteNonQuery(sql);
        }

        public int GetEmpIdByEmpNO(string EmpNO)
        {
            //var _TempTable = DataAccessor.ExeQuery("select ID from empbasic where EmployeeNo='" + EmpNO + "'");
            //if (_TempTable != null && _TempTable.Rows.Count > 0)
            //{
            //    int _EmpID = -1;
            //    int.TryParse(_TempTable.Rows[0][0].ToString(), out _EmpID);
            //    return _EmpID;                
            //}
            return -1;
        }
    }
}
